/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Controller;

import Module.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author CCD gruppe 2
 */
public class TeamController {
    /**
     * Object used to connect to the databse
     */
    private Database database = new Database();
    /**
     * Method used to register a team
     * @param team
     * @return
     */
    public int registerTeam(Team team){

        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            String[] autogen = {"id"};
            PreparedStatement statement = conn.prepareStatement(
                    "Insert into team values(DEFAULT,?)",autogen);
            statement.setString(1, team.getName());
            statement.executeUpdate();

            PreparedStatement statement2 = conn.prepareStatement(""
                    + "select * from team");
            ResultSet rs = statement2.executeQuery();
            int id=0;
            while(rs.next()){
                id = rs.getInt(1);
                
            }
            rs.close();
            conn.close();
            return id;
        }catch (Exception e) {
            System.err.println(e);

        }
        return 0;
    }
    /**
     * Method used to edit a team
     * @param team
     */
    public void editTeam(Team team){

        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            String[] autogen = {"id"};
            PreparedStatement statement = conn.prepareStatement(
                    "update team set teamName=? where teamId=? ");
            statement.setString(1, team.getName());
            statement.setInt(2,team.getId());
            statement.executeUpdate();
            conn.close();

        }catch (Exception e) {
            System.err.println(e);

        }
    }
    /**
     * Method used to get all tems to a project
     * @param id
     * @return
     */
    public ArrayList<Team> getTeamsToProject(int id){
        Connection conn = null;
        ArrayList<Team> teams = new ArrayList<Team>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            String[] autogen = {"id"};
            ArrayList<Integer> ids = new ArrayList<Integer>();
            PreparedStatement statement = conn.prepareStatement(""
                    + "select distinct teamId from usersProject where projectId =?");

            statement.setInt(1,id);
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                ids.add(rs.getInt(1));
            }
            rs.close();
            statement = conn.prepareStatement("select * from team where teamId=?");
            for(int i=0;i<ids.size();i++){
                statement.setInt(1, ids.get(i));
                rs = statement.executeQuery();
                while(rs.next()){
                    teams.add(new Team(rs.getInt(1),rs.getString(2)));
                }
                rs.close();
            }
            
            conn.close();
            return teams;

        }catch (Exception e) {
            System.err.println(e);

        }
        return teams;
    }
    /**
     * Method used to delete a team
     * @param teamId
     */
    public void deleteTeam(int teamId){
        Connection conn=null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("delete from team where teamId=?");
            PreparedStatement statement2 = conn.prepareStatement(""+
                    "delete from usersProject where teamId=?");
            statement.setInt(1, teamId);
            statement2.setInt(1, teamId);
            statement2.executeUpdate();
            statement.executeUpdate();
            conn.close();
        }catch(Exception e){
            System.err.println(e);
        }
    }
    /**
     * Method used to register teamgoals and teamdelivery to a session
     * @param teamId
     * @param sessionId
     * @param goal
     * @param delivery
     */
    public void registerTeamSession(int teamId, int sessionId, String goal, String delivery){
    Connection conn = null;
        try{
          
            conn = DriverManager.getConnection(database.getURL());
            String[] autogen = {"id"};
            PreparedStatement statement = conn.prepareStatement(
                    "insert into teamSession VALUES(?,?,?,?)");
            statement.setInt(1,teamId);
            statement.setInt(2, sessionId);
            statement.setString(3, goal);
            statement.setString(4, delivery);
            statement.executeUpdate();
            conn.close();

        }catch (Exception e) {
            System.err.println(e);

        }
    }
    /**
     * method used to get teamgoal, teamdelivery to a session
     * @param sessionId
     * @return
     */
    public ArrayList<Team> getTeamSession(int sessionId){
        Connection conn = null;
        ArrayList<Team> teams = new ArrayList<Team>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            String[] autogen = {"id"};
            PreparedStatement statement = conn.prepareStatement(
                    "select * from teamSession left join team on(team.teamId = teamSession.teamId) where sessionId=?");
            statement.setInt(1, sessionId);
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                teams.add(new Team(rs.getInt(1),rs.getString(6),rs.getString(3),rs.getString(4),rs.getInt(5)));
            }
            rs.close();
            conn.close();
            return teams;
        }catch (Exception e) {
            System.err.println(e);

        }
        return teams;
    
    }
    /**
     * Method used to edit the teamgoal, teamdelivery to a session
     * @param teamId
     * @param sessionId
     * @param goal
     * @param delivery
     */
    public void editTeamSession(int teamId, int sessionId, String goal, String delivery){
        Connection conn = null;
        try{

            conn = DriverManager.getConnection(database.getURL());
            String[] autogen = {"id"};
            PreparedStatement statement = conn.prepareStatement(
                    "update teamSession set teamgoal=?, teamdelivery=? where teamId=? and sessionId=?");
            statement.setString(1, goal);
            statement.setString(2, delivery);
            statement.setInt(3,teamId);
            statement.setInt(4, sessionId);
            statement.executeUpdate();
            conn.close();

        }catch (Exception e) {
            System.err.println(e);

        }
    }
    
}